﻿using CavinatorService.DAO.Base;
using CavinatorService.DAO.Extensao;
using CavinatorService.DAO.Parametros;
using CavinatorService.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CavinatorService.DAO
{
    /// <summary>
    /// Classe responsavel pela manipulacao de dados da resposta
    /// </summary>
    public class AnswerMapper : BaseMapper<Answer>
    {
        #region Properties

        /// <summary>
        /// Nome da tabela
        /// </summary>
        public override string TableName
        {
            get { return "Answer"; }
        }

        /// <summary>
        /// Id da tabela
        /// </summary>
        public override string[] Keys
        {
            get { return new string[] { "Id" }; }
        }

        #endregion

        #region Constructors

        public AnswerMapper()
            : base(SParam.Connection)
        {
        }

        #endregion

        #region Methods

        /// <summary>
        /// Atualiza uma resposta
        /// </summary>
        /// <param name="registro">Dados da resposta</param>
        public override void Atualizar(Answer registro)
        {
            string comando = string.Format(@"UPDATE {0}
                                               SET IdQuestion = @IDQUESTION,
                                                    IdItem = @IDITEM,
                                                    Answer = @ANSWER
                                             WHERE Id = @ID", TableName);

            List<IDbDataParameter> lstParam = new List<IDbDataParameter>();
            lstParam.Add(new SqlParameter("@IDQUESTION", registro.Question.Id));
            lstParam.Add(new SqlParameter("@IDITEM", registro.IdItem));
            lstParam.Add(new SqlParameter("@ANSWER", registro.AnswerValue));
            lstParam.Add(new SqlParameter("@ID", registro.Id));

            base.RetornarAfetadas(CommandType.Text, comando, lstParam);
        }

        /// <summary>
        /// Insere uma resposta
        /// </summary>
        /// <param name="registro">Dados da resposta</param>
        public override void Inserir(Answer registro)
        {
            string comando = string.Format(@"INSERT INTO {0}
                                                    (IdQuestion,
                                                    IdItem,
                                                    Answer)
                                                VALUES
                                                    (@IDQUESTION,
                                                    @IDITEM,
                                                    @ANSWER);
                                            SELECT @@IDENTITY;", TableName);

            List<IDbDataParameter> lstParam = new List<IDbDataParameter>();
            lstParam.Add(new SqlParameter("@IDQUESTION", registro.Question.Id));
            lstParam.Add(new SqlParameter("@IDITEM", registro.IdItem));
            lstParam.Add(new SqlParameter("@ANSWER", registro.AnswerValue));

            registro.Id = Convert.ToInt32(base.RetornarUnicoValor(CommandType.Text, comando, lstParam));
        }

        /// <summary>
        /// Exclui uma resposta
        /// </summary>
        /// <param name="id">Id da resposta</param>
        public override void Excluir(int id)
        {
            string comando = string.Format(@"DELETE FROM {0} 
                                             WHERE ID = @ID", TableName);

            List<IDbDataParameter> lstParam = new List<IDbDataParameter>();
            lstParam.Add(new SqlParameter("@ID", id));

            base.RetornarAfetadas(CommandType.Text, comando, lstParam);
        }

        /// <summary>
        /// Lista as respostas
        /// </summary>
        /// <returns>Lista de respostas</returns>
        public override List<Answer> Listar()
        {
            string comando = string.Format(@"SELECT A.*, Q.Value, I.Id AS IdItem FROM {0} A
                                                INNER JOIN Item I ON A.IdItem = I.Id
                                                INNER JOIN Question Q ON A.IdQuestion = Q.Id", TableName);

            return Util.CriarListaObjeto<Answer>(base.RetornarReader(CommandType.Text, comando));
        }

        /// <summary>
        /// Insere um lote de respostas
        /// </summary>
        /// <param name="lstAnswer">Lista de respostas</param>
        public void InserirLote(List<Answer> lstAnswer)
        {
            using (SqlConnection connection = (SqlConnection)base.Conectar())
            {
                using (SqlTransaction transacao = connection.BeginTransaction())
                {
                    try
                    {
                        SqlBulkCopy sbc = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, transacao);

                        sbc.ColumnMappings.Add("IdItem", "IdItem");
                        sbc.ColumnMappings.Add("IdQuestion", "IdQuestion");
                        sbc.ColumnMappings.Add("AnswerValue", "Answer");

                        sbc.DestinationTableName = TableName;

                        sbc.WriteToServer(Util.ConvertToDatatableSimple(lstAnswer));

                        transacao.Commit();
                    }
                    catch (Exception)
                    {
                        transacao.Rollback();
                    }
                }
            }
        }

        internal Dictionary<Question, List<Answer>> ListarPorPergunta()
        {
            string comando = string.Format(@"SELECT A.*, Q.Value FROM {0} A
                                                INNER JOIN Item I ON A.IdItem = I.Id
                                                INNER JOIN Question Q ON A.IdQuestion = Q.Id", TableName);

            //return Util.CriarDicionario<Question, Answer>(base.RetornarReader(CommandType.Text, comando), a => a.Question);
            Dictionary<Question, List<Answer>> dc = Util.CriarDicionario<Question, Answer>(base.RetornarReader(CommandType.Text, comando), a => a.Question);
            Dictionary<Question, List<Answer>> novo = new Dictionary<Question, List<Answer>>();

            foreach (Question q in dc.Keys)
            {
                if (!novo.ContainsKey(q))
                {
                    novo.Add(q, dc[q]);
                }
                else
                {
                    novo[q].AddRange(dc[q]);
                }
            }

            return novo;
        }


        #endregion
    }
}
